/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.ufpb.halloc.dados;

import br.com.ufpb.halloc.conexaoDB.Pool;
import br.com.ufpb.halloc.conexaoDB.PoolIF;
import br.com.ufpb.halloc.negocio.Departamento;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Rennan
 */
public class DepartamentoDAOPostgres implements DepartamentoDAO {
    
    private static DepartamentoDAOPostgres daoPostgres;
    private PoolIF pool;
    
    //SINGELTON
    public static DepartamentoDAOPostgres getInstance(){
        if (daoPostgres == null)
            daoPostgres = new DepartamentoDAOPostgres();
        return daoPostgres;
    }
    
    //CONSTRUTOR
    public DepartamentoDAOPostgres(){
        this.pool = new Pool();
    }
    
    @Override
    public void insert(Departamento d) {
        Connection c = this.pool.getConnection();
        PreparedStatement ps;
        
        String statement = "INSERT INTO departamento (nomedepartamento, telefone,"
                + " coddepartamento, codcentro) VALUES (?, ?, ?, ?);";
        try {
            ps = c.prepareStatement(statement);
            ps.setString(1, "nomedepartamento");
            ps.setString(2, "telefone");
            ps.setString(3, "coddepartamento");
            ps.setString(4, "codcentro");
            
            ps.execute();
            ps.close();
            
        } catch (SQLException ex) {
            Logger.getLogger(DepartamentoDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            this.pool.liberarConnection(c);
        }
        
    }

    @Override
    public void remover(Departamento d) {
        Connection c = this.pool.getConnection();
        
        PreparedStatement p;
        
        String statement = "DELETE FROM departamento WHERE coddepartamento = ?;";
        try {
            p = c.prepareStatement(statement);
            p.setString(1, d.getCodDepartamento());
            p.executeUpdate();
            p.close();
        } catch (SQLException ex) {
            Logger.getLogger(DepartamentoDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            this.pool.liberarConnection(c);
        }
        
    }

    @Override
    public void remover(String cod) {
        Connection c = this.pool.getConnection();
        PreparedStatement p;
        String statement = "DELETE FROM departamento WHERE coddepartamento = "
                            + cod + ";";
        try {
            p = c.prepareStatement(statement);
            p.executeUpdate();
            p.close();
        } catch (SQLException ex) {
            Logger.getLogger(DepartamentoDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            this.pool.liberarConnection(c);
        }
        
    }

    @Override
    public void atualizar(Departamento d) {
        Connection c = this.pool.getConnection();
        PreparedStatement p;
        String statement = "UPDATE departamento SET nomedepartamento = ?, telefone = ?,"
                + " codcentro = ? WHERE coddepartamento = ?;";
        try {
            p = c.prepareStatement(statement);
            p.setString(1, d.getNomeDepartamento());
            p.setString(2, d.getTelefone());
            p.setString(3, d.getCodCentro());
            p.setString(4, d.getCodDepartamento());
            
            p.execute();
            p.close();
        } catch (SQLException ex) {
            Logger.getLogger(DepartamentoDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            this.pool.liberarConnection(c);
        }
        
    }

    @Override
    public Departamento getDepartamento(String cod) {
        Connection c = this.pool.getConnection();
        PreparedStatement p;
        ResultSet r;
        String statement = "SELECT * FROM departamento WHERE coddepartamento = ?;";
        Departamento d = null;
        
        try {
            p = c.prepareStatement(statement);
            p.setString(1, cod);
            
            r = p.executeQuery();
            d = new Departamento(r.getString("nomedepartamento"), r.getString("telefone"),
                                 r.getString("coddepartamento"), r.getString("codcentro"));
            p.close();
        } catch (SQLException ex) {
            Logger.getLogger(DepartamentoDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            this.pool.liberarConnection(c);
        }
        
        return d;
        
    }

    @Override
    public Departamento getDepartamento(Departamento d) {
        Connection c = this.pool.getConnection();
        PreparedStatement p;
        ResultSet r;
        String statement = "SELECT * FROM departamento WHERE coddepartamento = ?;";
        Departamento dep = null;
        
        try {
            p = c.prepareStatement(statement);
            p.setString(1, d.getCodDepartamento());
            
            r = p.executeQuery();
            dep = new Departamento(r.getString("nomedepartamento"), r.getString("telefone"),
                                 r.getString("coddepartamento"), r.getString("codcentro"));
            p.close();
        } catch (SQLException ex) {
            Logger.getLogger(DepartamentoDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            this.pool.liberarConnection(c);
        }
        
        return dep;
    }

    @Override
    public List<Departamento> todosDepartamentos() {
        Connection c = this.pool.getConnection();
        PreparedStatement p;
        String statement = "SELECT * FROM departamento;";
        ResultSet r;
        
        List<Departamento> deps = new ArrayList<Departamento>();
        try {
            p = c.prepareStatement(statement);
            r = p.executeQuery();
            while (r.next()){
                Departamento d = new Departamento(
                                r.getString("nomedepartamento"),
                                r.getString("telefone"), 
                                r.getString("coddepartamento"),
                                r.getString("codcentro"));
                deps.add(d);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DepartamentoDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            this.pool.liberarConnection(c);
        }
        
        return deps;
    }

    @Override
    public int size() {
        Connection c = this.pool.getConnection();
        PreparedStatement p;
        ResultSet r;
        String statement = "SELECT COUNT(*) as total FROM departamento;";
        int total = 0;
        
        try {
            p = c.prepareStatement(statement);
            r = p.executeQuery();
            r.next();
            total = r.getInt("total");
            p.close();
        } catch (SQLException ex) {
            Logger.getLogger(DepartamentoDAOPostgres.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            this.pool.liberarConnection(c);
        }
        
        return total;
    }
    
}
